with ord as (
select 
        id as order_id
       ,waybill_id as waybill_no
       ,order_source_code
       ,order_source_name
       ,pick_network_code
       ,dispatch_network_code
       ,input_time
       ,is_delete
       ,order_status_code
       ,to_date(input_time) as dt
from jms_ods.yl_oms_oms_order   
where dt = '{{ execution_date | cst_ds }}'
and to_date(input_time) = '{{ execution_date | cst_ds }}'
),
network as (
select 
        code
        ,name
        ,franchisee_code
        ,franchisee_name
        ,agent_code
        ,agent_name
        ,provider_id
        ,provider_desc
        ,city_id
        ,city_desc
        ,area_id
        ,area_desc
from jms_dim.dim_sys_network_detail_dt
where dt = '{{ execution_date | cst_ds }}'
)
insert overwrite table jms_dwd.dwd_order_detail_dt partition(dt)
select  input_time                  as input_time
       ,date(input_time)            as input_date
       ,order_id                    as order_id
       ,waybill_no                  as waybill_no
       ,order_source_code           as order_source_code
       ,order_source_name           as order_source_name
       ,pick_network_code           as pick_network_code
       ,start_net.name              as pick_network_name
       ,start_net.franchisee_code   as pick_franchisee_code
       ,start_net.franchisee_name   as pick_franchisee_name
       ,start_net.agent_code        as pick_agent_code
       ,start_net.agent_name        as pick_agent_name
       ,start_net.provider_id       as sender_province_id
       ,start_net.provider_desc     as sender_province_name
       ,start_net.city_id           as sender_city_id
       ,start_net.city_desc         as sender_city_name
       ,start_net.area_id           as sender_area_id
       ,start_net.area_desc         as sender_area_name
       ,dispatch_network_code       as dispatch_network_code
       ,end_net.name                as dispatch_network_name
       ,end_net.franchisee_code     as dispatch_franchisee_code
       ,end_net.franchisee_name     as dispatch_franchisee_name
       ,end_net.agent_code          as dispatch_agent_code
       ,end_net.agent_name          as dispatch_agent_name
       ,end_net.provider_id         as receiver_province_id
       ,end_net.provider_desc       as receiver_province_name
       ,end_net.city_id             as receiver_city_id
       ,end_net.city_desc           as receiver_city_name
       ,end_net.area_id             as receiver_area_id
       ,end_net.area_desc           as receiver_area_name
       ,is_delete
       ,order_status_code
       ,dt
from ord 
left join network start_net on coalesce(ord.pick_network_code,waybill_no)     = start_net.code
left join network end_net   on coalesce(ord.dispatch_network_code,waybill_no) = end_net.code
distribute by dt,pmod(hash(rand()),10)
 ;